import { TableHelper } from './table-helper';
import Decimal from 'decimal.js';
const DAY_MUNIS = 24 * 60 * 60 * 1000;
const BASE_OPERATE_PRIORITY = {
    '+': 50,
    "-": 50,
    "*": 60,
    "/": 60,
    "%": 60,
    "**": 70,
    "!": 80,
    "==": 90,
    "!=": 90,
    ">": 90,
    "<": 90,
    ">=": 90,
    "<=": 90
}
export type OperateSymbol = (keyof typeof BASE_OPERATE_PRIORITY)
export interface IError {
    info: any;
    toString: () => string;
}
export interface IFormulaItem {
    type: string;
}
export interface IPositionFullRow extends IFormulaItem {
    type: 'rowPosition',
    value: number;
    /**
     * true 相对 false 绝对
     */
    isRelative: boolean;
}
export interface IPositionFullCol extends IFormulaItem {
    type: 'colPosition',
    value: number;
    isRelative: boolean;
}
export interface IPosition extends IFormulaItem{
    type: 'position'
    row: number;
    rowIsRelative: boolean;
    col: number;
    colIsRelative: boolean;
}
export interface IOperate extends IFormulaItem {
    type: 'operate';
    value: OperateSymbol;
}
interface IExcelValue {
    rangeId?: string;
    range?: string;
    row?: number;
    col?: number;
}
export interface IValueString extends IFormulaItem, IExcelValue {
    type: 'value';
    dataType: 'string';
    value: string;
    range?: string;
}
export interface IValueNumber extends IFormulaItem, IExcelValue {
    type: 'value';
    dataType: 'number';
    value: number
}
export interface IValueBoolean extends IFormulaItem, IExcelValue {
    type: 'value';
    dataType: 'boolean';
    value: boolean;
}
export interface IBrackets extends IFormulaItem {
    type: 'brackets';
    value: 'left'|'right'
}
export interface IFunction extends IFormulaItem {
    type: 'function';
    value: string;
}
export interface IPlusMinus extends IFormulaItem {
    type: 'plusMinus',
    value: 'plus'|'minus'
}
export interface IFormulaError extends IFormulaItem  {
    type: 'error';
    msg: string;
}
export interface IKeywords extends IFormulaItem {
    type: 'keyworks';
    value: string;
}
export interface ISpecial extends IFormulaItem {
    type: 'special',
    value: ':'|','
}
export type TAllTypes = IPosition | IOperate | IValueString | IValueNumber 
| IValueBoolean | IBrackets | IFunction | IPositionFullRow|IPositionFullCol|IPlusMinus
|IFormulaError|IKeywords|ISpecial;
/**
 * '#div/0!' 当公式被零除时，将产生错误值#div/0！
 * '#N/A' 当在函数或公式中没有可用的数值时，将产生错误值#N/A
 * '#NAME?' 在公式中使用了Microsoft Excel不能识别的文本
 * '#NULL!' 试图为两个并不相交的区域指定交叉点时，将产生以上错误
 * '#NUM!' 当公式或函数中某些数字有问题时，将产生该错误信息
 * '#REF!' 当单元格引用无效时，将产生该错误信息
 * '#value!' 当使用错误的参数或运算对象类型时，或当自动更改公式功能不能更正公式时，将产生该错误信息
 */
const ErrorInfo = {
    '#DIV/0!': '当公式被零除时，将产生错误值#div/0!',
    '#N/A': '当在函数或公式中没有可用的数值时，将产生错误值#N/A',
    '#NAME?': '在公式中使用了Microsoft Excel不能识别的文本',
    '#NULL!': '试图为两个并不相交的区域指定交叉点时，将产生以上错误',
    '#NUM!': '当公式或函数中某些数字有问题时，将产生该错误信息',
    '#REF!': '当单元格引用无效时，将产生该错误信息',
    '#VALUE!': '当使用错误的参数或运算对象类型时，或当自动更改公式功能不能更正公式时，将产生该错误信息',
    '#UNKOWN!': '未知错误'
}
class FormulaErr {
    protected info!: any;
    constructor(protected msg: keyof typeof ErrorInfo, info?: any){
        if(!info) {
            if(typeof info === 'object') {
                try {
                    this.info = JSON.parse(JSON.stringify(info))
                } catch (error) {
                    this.info = {originInfo: info, error};
                }
            } else {
                this.info = info;
            }
        }
    }
    toString() {
        return this.msg;
    }
    get value() {
        return this.msg;
    }
}
type ErrorInfo = null|FormulaErr;
type ParseFunctionResult = [ErrorInfo, TAllTypes, number]
type ParseFunction = (str: string, startIdx: number, pastParse: TAllTypes[]) => ParseFunctionResult;
let KEYWORDS = {
    'TRUE': true,
    'FALSE': false
}


const BASE_OPERATE: OperateSymbol[] = [
    '+','-','*','/','==','!=','!','>',">=",'<','<=', '**', '%'
];
interface IParseItem {
    /**
     * @param str {string} 原始字符串
     * @param start {number}
     */
    (str: string, start: number):[IError|null, number, boolean, TAllTypes[]]
}
/**
 * 
 * @param str st
 * @param start 
 */
const parseCell: ParseFunction = (str: string, start: number, renstu: any) => {
    let endIdx = str.length;
    for(let i = start; i < str.length; i++) {
        if(!/^[A-Z0-9\$]$/.test(str[i])) {
            endIdx = i;
            break;
        }
    }
    let subStr = str.substring(start, endIdx);
    if(str[endIdx] !== '(') {
        if(/^\$*[A-Z]+((\${0}\d*)|(\${1}\d+))$/.test(subStr)) {
            let rowInfo = subStr.match(/^\$*[A-Z]*/g)[0];
            let colInfo = subStr.match(/\$*\d*$/g)[0];
            let rowIsRelative = rowInfo.indexOf('$') === -1;
            let rowNum = TableHelper.mapColToNum(rowInfo.replace('$', ''));
            if(colInfo === null) {
                let val: IPositionFullRow = {
                    type: 'rowPosition',
                    value: rowNum,
                    isRelative: rowIsRelative
                }
                return [null, val, endIdx - 1];
            } else {
                let val: IPosition = {
                    type: "position",
                    col: Number(colInfo.replace('$', '')),
                    colIsRelative: colInfo.indexOf('$') === -1,
                    row: rowNum,
                    rowIsRelative
                }
                return [null, val, endIdx - 1];
            }
        }
    }
    if(KEYWORDS[subStr as keyof typeof KEYWORDS] !== undefined) {
        let val: IKeywords = {
            type: 'keyworks',
            value: subStr
        }
        return [null, val, endIdx - 1];
    }
    let val: IFunction = {
        type: 'function',
        value: subStr
    }
    return [null, val, endIdx - 1];
}
type SplitSymbol = '"'|"'";
const parseString: ParseFunction = (str: string, start: number) => {
    const stack: ('"'|"'")[] = [];
    stack.push(str[start] as SplitSymbol);
    for(let i = start+1; i < str.length; i++) {
        if(str[i] === '"' || str[i] === "'") {
            if(stack[stack.length - 1] === str[i]) {
                stack.pop();
                if(stack.length === 0) {
                    let val: IValueString = {
                        type: 'value',
                        dataType: 'string',
                        value: str.substring(start + 1, i)
                    };
                    return [null, val, i];
                }
            } else {
                stack.push(str[i] as SplitSymbol);
            }
        }  
    }
    return [new FormulaErr('#NAME?'), {type: 'error', msg: 'parseString'}, str.length -1];
}
const paseOperate:ParseFunction = (str: string, start: number, result: TAllTypes[]) => {
    let pastParse = result[result.length -1] as IBrackets | IOperate;
    if((start === 0) || ((str[start] === '+'||str[start] === '-') && result.length > 0 && ((pastParse.type === 'brackets' && pastParse.value === 'left')) || pastParse.type === 'operate')) {
        let val:IPlusMinus = {
            type: 'plusMinus',
            value: str[start] === '+' ? 'plus':'minus'
        }
        return [null, val, start];
    }
    // if(str[start] === '>') {
    //     console.log(['>', '<', '*'].indexOf(str[start]) !== -1,str[start + 1],BASE_OPERATE.indexOf(str[start + 1] as OperateSymbol) !== -1)
    // }
    if(start < str.length - 1 && ['>', '<', '*'].indexOf(str[start]) !== -1 && (BASE_OPERATE.indexOf(str[start + 1] as OperateSymbol) !== -1 || str[start + 1] === '=') && ['+', '-'].indexOf(str[start+1]) === -1) {
        console.log('==', str.substring(start, start+2))
        let val: IOperate = {
            type: 'operate',
            value: str.substring(start, start+2) as OperateSymbol
        }
        return [null, val, start+1];
    }
    if(str[start] === '!' || str[start] === '=') {
        if(str[start + 1] !== '=') {
            // throw error;
        }
        let val: IOperate = {
            type: 'operate',
            value: str.substring(start, start+1) as OperateSymbol
        }
        return [null, val, start+1];
    }
    
    let val: IOperate = {
        type: 'operate',
        value: str[start] as OperateSymbol
    }
    return [null, val, start];
}
const parseNumber: ParseFunction = (str: string, start: number) => {
    let endIdx = str.length - 1;
    for(let i = start; i < str.length; i++) {
        if(!(/[0-9]/.test(str[i])) && str[i] && str[i] !== '.') {
            endIdx = i - 1;
            break;
        }
    }
    let _val = str.substring(start, endIdx+1);
    if((start > 0 && str[start - 1] === ':') || (endIdx < str.length -1 && str[endIdx + 1] === ':')) {
        let val: IPositionFullRow = {
            type: 'rowPosition',
            value: Number(_val),
            isRelative: true
        }
        return [null, val, endIdx];
    }
    let val: IValueNumber = {
        type: 'value',
        dataType: 'number',
        value: Number(_val)
    }
    return [null, val, endIdx];
}

export function parse(str: string) {
    let result: TAllTypes[] = [];
    let err = null;
    for(let i = 0; i < str.length; i++) {
        if(str[i] === ' ') continue;
        if(str[i] === '(') {
            result.push({type:'brackets', value: 'left'});
            continue;
        }
        if(str[i] === ')') {
            result.push({type:'brackets', value: 'right'});
            continue;
        }
        if(str[i] ===':') {
            result.push({
                type: "special",
                value: ':'
            });
            continue;
        }
        if(str[i] === ',') {
            result.push({
                type: "special",
                value: ','
            });
            continue;
        }
        if(str[i] === '"' || str[i] === `'`) {
            let [err, val, newStartIdx] = parseString(str, i, result);
            if(!err) {
                result.push(val);
                i = newStartIdx;
                continue;
            }
        }
        if(BASE_OPERATE.indexOf(str[i] as OperateSymbol) !== -1) {
            let [err, val, newStartIdx] = paseOperate(str, i, result);
            if(!err) {
                result.push(val);
                i = newStartIdx;
                continue;
            }
        }
        if(/[0-9]/.test(str[i])) {
            let [err, val, newStartIdx] = parseNumber(str, i, result);
            if(!err) {
                result.push(val);
                i = newStartIdx;
                continue;
            }
        }
        if(/[A-Z]/.test(str[i]) ||  str[i] === '$') {
            let [err, val, newStartIdx] = parseCell(str, i, result);
            if(!err) {
                result.push(val);
                i = newStartIdx;
                continue;
            }
        }
        
    }
    return result;
}
const safeWhile = (func: (idx: number) => boolean, max = 1000) => {
    let idx = 0;
    while(true) {
        let manualBreck = func(idx);
        if(idx++ === max || manualBreck) {
            break;
        }
    }
}
export function rebuildCaculate(calcArr: TAllTypes[]) {
    let result: TAllTypes[] = [];
    let operates: TAllTypes[] = [];
    for(let i = 0; i < calcArr.length; i++) {
        if(calcArr[i].type === 'value' || calcArr[i].type === 'keyworks' || calcArr[i].type === 'position' 
        || calcArr[i].type === 'rowPosition' || calcArr[i].type === 'colPosition') {
            result.push(calcArr[i]);
            if(operates.length > 0) {
                let preOperate = operates[operates.length - 1];
                if(preOperate.type === 'special' && preOperate.value === ':') {
                    result.push(operates.pop())
                }
            }
            continue;
        }
        if(calcArr[i].type === 'brackets') {
            let currentOperate = calcArr[i] as IBrackets;
            if(currentOperate.value === 'left') {
                operates.push(currentOperate);
                continue;
            }
            safeWhile(() => {
                if(operates.length === 0) {
                    return true;
                }
                let tempOpreate = operates.pop()  as IBrackets;
                if(tempOpreate.type === 'brackets' && tempOpreate.value === 'left') {
                    if(operates.length > 0 && operates[operates.length - 1].type === 'function') {
                        result.push(operates.pop());
                        if(operates.length > 0 && operates[operates.length - 1].type ==='plusMinus') {
                            result.push(operates.pop());
                        }
                    }
                    return true;
                } else {
                    result.push(tempOpreate);
                }
                return false;
            });
            if(operates.length > 0 && operates[operates.length - 1].type === 'plusMinus') {
                result.push(operates.pop())
            }
            continue;
        }
        if(calcArr[i].type === 'plusMinus') {
            if(operates.length === 0) {
                operates.push(calcArr[i]);
                continue;
            }
            if(operates[operates.length -1].type === 'operate') {
                operates.push(calcArr[i]);
                continue;
            }
        }
        if(calcArr[i].type === 'operate') {
            let currentOperate = calcArr[i] as IOperate;
            if(operates.length === 0) {
                operates.push(currentOperate);
                continue;
            }
            let preOperate = operates[operates.length - 1];
            if(preOperate.type === 'plusMinus') {
                result.push(operates.pop());
                continue;
            }
            if(preOperate.type !== 'operate') {
                operates.push(currentOperate);
                continue;
            }
            
            let priority = BASE_OPERATE_PRIORITY[currentOperate.value];
            safeWhile(() => {
                if(operates.length === 0 || operates[operates.length - 1].type !== 'operate') {
                    operates.push(currentOperate);
                    return true;
                }
                let prepPriority = BASE_OPERATE_PRIORITY[currentOperate.value];
                if(priority <= prepPriority) {
                    operates.push(currentOperate);
                    return true;
                } else {
                    result.push(operates.pop());
                }
                return false;
            });
        }
        if(calcArr[i].type === 'function') {
            operates.push(calcArr[i]);
        }
        if(calcArr[i].type === 'special' && (calcArr[i] as ISpecial).value === ':') {
            safeWhile(() => {
                if(operates.length === 0) {
                    return true;
                }
                let preOperate = operates[operates.length - 1];

                if(preOperate.type === 'brackets' || preOperate.type === 'operate') {
                    return true;
                }
                result.push(operates.pop());
            });
            operates.push(calcArr[i])
        }
    }
    return result.concat(operates.reverse());
}
export function calculate(operate: OperateSymbol, vals: any[]): TAllTypes {
    if(operate === '+') {
        if(isNaN(Number(vals[0])) || isNaN(Number(vals[0]))) {
            throw new FormulaErr('#NUM!', {operate: '+', info: vals});
        }
        return {type: 'value',value: new Decimal(vals[0] + vals[1]).toNumber(), dataType: 'number'};
    }
    if(operate === '-') {
        if(isNaN(Number(vals[0])) || isNaN(Number(vals[0]))) {
            throw new FormulaErr('#NUM!', {operate: '-', info: vals});
        }
        return {type: 'value', dataType: 'number',value: new Decimal(vals[0] - vals[1]).toNumber()};
    }
    if(operate === '*') {
        if(isNaN(Number(vals[0])) || isNaN(Number(vals[0]))) {
            throw new FormulaErr('#NUM!', {operate: '+', info: vals});
        }
        return {type: 'value', dataType: 'number',value: new Decimal(vals[0] * vals[1]).toNumber()}; 
    }
    if(operate === '/') {
        if(isNaN(Number(vals[0])) || isNaN(Number(vals[0]))) {
            throw new FormulaErr('#NUM!', {operate: '+', info: vals});
        }
        if(Number(vals[1]) === 0) {
            throw new FormulaErr('#DIV/0!', {operate: '/', info: vals})
        }
        return {type: 'value',dataType: 'number', value: new Decimal(vals[0] / vals[1]).toNumber()}; 
    }
    if(operate === '**') {
        if(isNaN(Number(vals[0])) || isNaN(Number(vals[0]))) {
            throw new FormulaErr('#NUM!', {operate: '+', info: vals});
        }
        return {type: 'value',dataType: 'number', value: Decimal.pow(vals[0], vals[1]).toNumber()};
    }
    if(operate === '%') {
        if(isNaN(Number(vals[0])) || isNaN(Number(vals[0]))) {
            throw new FormulaErr('#NUM!', {operate: '+', info: vals});
        }
        return {type: 'value',dataType: 'number', value: new Decimal(vals[0] % vals[1]).toNumber()}; 
    }
    if(operate === '!=') {
        if(vals[0] === undefined || vals[1] === undefined) {
            throw new FormulaErr('#N/A', {operate: '!=', info: vals})
        }
        return {type: 'value',dataType: 'boolean',value: vals[0] != vals[1]}
    }
    if(operate === '==') {
        if(vals[0] === undefined || vals[1] === undefined) {
            throw new FormulaErr('#N/A', {operate: '!=', info: vals})
        }
        return {type: 'value',dataType: 'boolean',value: vals[0] == vals[1]} 
    }
    if(operate === '>') {
        if(vals[0] === undefined || vals[1] === undefined) {
            throw new FormulaErr('#N/A', {operate: '!=', info: vals})
        }
        return {type: 'value',dataType: 'boolean',value: vals[0] > vals[1]}
    }
    if(operate === '>=') {
        if(vals[0] === undefined || vals[1] === undefined) {
            throw new FormulaErr('#N/A', {operate: '!=', info: vals})
        }
        return {type: 'value',dataType: 'boolean',value: vals[0] >= vals[1]}
    }
    if(operate === '<') {
        if(vals[0] === undefined || vals[1] === undefined) {
            throw new FormulaErr('#N/A', {operate: '!=', info: vals})
        }
        return {type: 'value',dataType: 'boolean',value: vals[0] < vals[1]}
    }
    if(operate === '<=') {
        if(vals[0] === undefined || vals[1] === undefined) {
            throw new FormulaErr('#N/A', {operate: '!=', info: vals})
        }
        return {type: 'value',dataType: 'boolean',value: vals[0] <= vals[1]}
    }
}
export type IFunctionCalculate = {
    [key: string]: (args: TAllTypes[]) => TAllTypes[];
}
type IAllPosition = IPosition|IPositionFullRow|IPositionFullCol;
export abstract class AFormula {
    protected calcArr: TAllTypes[] = [];
    protected result: TAllTypes;
    protected abstract funcCalculate: IFunctionCalculate; 
    public updateFormulaStr(str: string) {
        this.calcArr = rebuildCaculate(parse(str));
        this.valueChange();
    };
    public valueOf() {
        if(this.result.type === 'value') {
            return this.result.value;
        }
    }
    public toString() {
        if(!this.result && this.result.type === 'value') {
            return this.result.value;
        }
        return '';
    }
    public valueChange() {
        let copyCalcArr = [...this.calcArr];
        console.log(copyCalcArr)
        try {
            safeWhile(() => {
                if(copyCalcArr.length < 2) {
                    return true;
                }
                for(let i = 0; i < copyCalcArr.length; i++) {
                    if(copyCalcArr[i].type !== 'value') {
                        if(copyCalcArr[i].type === 'special' && (copyCalcArr[i] as ISpecial).value === ':') {
                            let tempValues = this.getRangeValue(copyCalcArr[i - 2] as IAllPosition, copyCalcArr[i - 1]as IAllPosition);
                            copyCalcArr.splice(0, 3, ...tempValues);
                            return false;
                        }
                        if(copyCalcArr[i].type === 'operate') {
                            let val1 = this.getValue(copyCalcArr[i-2]) as IValueNumber[];
                            let val2 = this.getValue(copyCalcArr[i-1]) as IValueNumber[];
                            console.log(val1, val2)
                            if(val1.length !== 1 || val2.length !== 1) {
                                throw new FormulaErr('#REF!', {info: [copyCalcArr[i-2], copyCalcArr[i-1]]});
                            }
                            console.log((copyCalcArr[i] as IOperate).value)
                            let _result = calculate((copyCalcArr[i] as IOperate).value, [val1[0].value, val2[0].value]);
                            console.log(_result)
                            copyCalcArr.splice(0, 3,_result);
                            return false;
                        }
                        if(copyCalcArr[i].type === 'function') {
                            let vals = copyCalcArr.slice(0, i);
                            let funcName = (copyCalcArr[i] as IFunction).value;
                            let result = this.matchFuncCalculate(funcName, vals);
                            copyCalcArr.splice(0, i + 1, ...result);
                            return false;
                        }
                        throw new FormulaErr('#UNKOWN!', {info: {msg: '未匹配任何操作', value: copyCalcArr[i]}});
                    }
                }
            }, 10000)
        } catch (error) {
            console.log(error)
        }
        console.log(copyCalcArr)
    }
    protected getValue(val: TAllTypes) {
        if(val.type === 'value') {
            return [val];
        }
        if(val.type === 'position' || val.type === 'rowPosition' || val.type ==='colPosition') {
            return this.getPositionValue(val);
        }
        if(__ENV__ === 'development') {
            console.log(``)
        }
        return [val];
    }
    protected abstract getPositionValue(val: IAllPosition): (TAllTypes&Required<IExcelValue>)[];
    protected abstract getRangeValue(val1: IAllPosition,val2: IAllPosition): (TAllTypes&Required<IExcelValue>)[];
    protected matchFuncCalculate(func: string, vals: TAllTypes[]) {
        if(typeof this.funcCalculate[func] !== 'function') {
            throw new FormulaErr('#NAME?', {info: {funcName: func, msg: 'calculat function not found!'}});
        }
        let result = this.funcCalculate[func](vals);
        if(!Array.isArray(result)) {
            throw new FormulaErr('#NAME?', {info: {funcName: func, msg: 'calculat result is not Array!'}});
        }
        return result;
    }
}
function parseToNumber(val: IValueNumber | IValueString): IValueNumber {
    if(val.type === 'value' && val.dataType === 'number') {
        return val as IValueNumber;
    }
    if(val.type === 'value' && val.dataType === 'string') {
        let _val = Number(val.value);
        if(isNaN(_val)) {
            return {
                type: 'value',
                dataType: 'number',
                value: 0
            }
        } else {
            return {
                type: 'value',
                dataType: 'number',
                value: _val
            }
        }
    }
    return {
        type: 'value',
        dataType: 'number',
        value: 0
    }
}
function parseToString(val: IValueNumber | IValueString|IValueBoolean): IValueString {
    if(val.type === 'value' && val.dataType === 'string') {
        return val as IValueString;
    }
    if(val.type === 'value' && val.dataType === 'number') {
        return {
            type: 'value',
            dataType: 'string',
            value: String(val.value)
        }
    }
    if(val.type === 'value' && val.dataType === 'boolean') {
        return {
            type: 'value',
            dataType: 'string',
            value: String(val.value)
        }
    }
    return {
        type: 'value',
        dataType: 'string',
        value: ''
    }
}
export type mapAnyTypeToRangeValue = TAllTypes&IExcelValue
export function formatValsToGroup(vals: mapAnyTypeToRangeValue[]) {
    if(vals.length === 0) return [];
    let result: mapAnyTypeToRangeValue[][] = [];
    let currentGroup: mapAnyTypeToRangeValue[] = [];
    let currentGroupSymbol = vals[0].rangeId
    for(let val of vals) {
        if(val.range !== currentGroupSymbol) {
            result.push(currentGroup);
            currentGroup = [];
        }
        currentGroup.push(val);
    }
    result.push(currentGroup);
    return result;
}
function groupByCol<T extends (Required<IExcelValue>&(IValueBoolean|IValueNumber|IValueString))>(vals: T[]) {
    let temp: {[key: number]: T[]} = {};
    for(let val of vals) {
        if(temp[val.col] === undefined) {
            temp[val.col] = [];
        }
        temp[val.col].push(val);
    }
    let result: {col: number, vals: T[]}[] = [];
    for(let key in temp) {
        result.push({
            col: Number(key),
            vals: temp[key]
        })
    }
    return result.sort((a, b) => a.col - b.col);
}
function isEqualVal(v1: IValueBoolean|IValueNumber|IValueString, v2: IValueBoolean|IValueNumber|IValueString) {
    if(v1.dataType === v2.dataType) {
        return v1.value === v2.value;
    }
    if((v1.dataType === 'string' || v1.dataType === 'number') && (v2.dataType === 'string' || v2.dataType === 'number')) {
        return String(v1.value) === String(v2.value);
    }
    return false;
}
export interface FuncCalculate {
    [key: string]: (args: TAllTypes[]) => TAllTypes[];
}

export function typeofValue(val: TAllTypes) {
    if(val.type === 'value') {
        return val.dataType;
    }
    return 'notValue';
}
export class FuncCalculate {

    public SUM(vals: TAllTypes[]): TAllTypes[] {
        let sum = 0;
        for(let val of vals) {
            if(val.type == 'value' && (val.dataType === 'number' || val.dataType === 'string')) {
                let _val = Number(val.value);
                if(isNaN(_val) === false) {
                    sum = (calculate('+', [sum, _val]) as IValueNumber).value;
                }
            }
        }
        return [
            {
                type: 'value',
                dataType: 'number',
                value: sum
            }
        ];
    }
    public SUMIF(vals: TAllTypes[]): TAllTypes[] {
        let groupVals = formatValsToGroup(vals);
        if(groupVals.length !== 3) {
            throw new FormulaErr('#N/A', {vals: vals});
        }
        type valueFromCell = Required<IExcelValue>&(IValueBoolean|IValueNumber|IValueString);
        let rangeVals = <any>groupVals[0] as valueFromCell[];
        let criteria = <any>groupVals as (IValueBoolean|IValueNumber|IValueString)[];
        let sumVals: TAllTypes[] = [];
        let group1 = groupByCol(rangeVals);
        let group2 = groupByCol(criteria as valueFromCell[]);
        let group3 = groupByCol(groupVals[2] as valueFromCell[]);
        if(criteria[0].rangeId !== undefined) { // 范围需要一一对应
            if(!Array.isArray(group1) ||!Array.isArray(group2)||!Array.isArray(group3)) {
                throw new FormulaErr('#N/A', {vals: vals, msg: 'group1,group2,group3', groups: [group1, group2,group3]});
            }
            for(let i = 0; i < Math.min(group1.length, group2.length, group3.length); i++) {
                let rows1 = group1[i];
                let rows2 = group2[i];
                for(let cell of rows1.vals) {
                    let find = rows2.vals.find(item => item.row === cell.row);
                    if(!find) continue;
                    if(isEqualVal(cell, find)) {
                        let _find = group3[i].vals.find(item => item.row === cell.row);
                        _find && sumVals.push(_find);
                    }
                }
            }
        } else {
            if(criteria[0].col !== undefined || criteria[0].row !== undefined) { // 来源于单元格的值
                for(let i = 0; i < Math.min(group1.length, group3.length); i++) {
                    for(let cell of group1[i].vals) {
                        if(isEqualVal(cell, criteria[0])) {
                            let _find = group3[i].vals.find(item => item.row === cell.row);
                            _find && sumVals.push(_find);
                        }
                    }
                }
            } else { // 直接输入的值
                for(let i = 0; i < Math.min(group1.length, group3.length); i++) {
                    for(let cell of group1[i].vals) {
                        // 未判断特殊字符>5等情况
                        if(isEqualVal(cell, criteria[0])) {
                            let _find = group3[i].vals.find(item => item.row === cell.row);
                            _find && sumVals.push(_find);
                        }
                    }
                }
            }
        }
        return this.SUM(sumVals);
    }
    public AVGRAGE(vals: TAllTypes[]): TAllTypes[] {
        let sum = 0;
        let count = 0;
        for(let val of vals) {
            if(val.type == 'value' && (val.dataType === 'number' || val.dataType === 'string')) {
                let _val = Number(val.value);
                if(isNaN(_val) === false) {
                    sum +=  _val;
                    count++;
                }
            }
        }
        if(count === 0) {
            return [{
                type: 'value',
                dataType: 'number',
                value: 0
            }]
        }
        return [calculate('/', [sum, count])];
    }
    public AND(vals: TAllTypes[]): TAllTypes[] {
        if(vals.length === 0) {
            throw new FormulaErr('#N/A', {vals: vals});
        }
        for(let val of vals) {
            if(!Boolean((val as IValueBoolean).value)) {
                return [
                    {
                        type: 'value',
                        dataType: 'boolean',
                        value: false
                    }
                ]
            }
        }
        return [{
            type: 'value',
            dataType: 'boolean',
            value: true
        }];
    }
    public OR(vals: TAllTypes[]): TAllTypes[] {
        if(vals.length === 0) {
            throw new FormulaErr('#N/A', {vals: vals});
        }
        for(let val of vals) {
            if(Boolean((val as IValueBoolean).value)) {
                return [
                    {
                        type: 'value',
                        dataType: 'boolean',
                        value: true
                    }
                ]
            }
        }
        return [{
            type: 'value',
            dataType: 'boolean',
            value: false
        }];
    }
    public IF(vals: TAllTypes[]): TAllTypes[] {
        if(vals.length !== 3) {
            throw new FormulaErr('#N/A', {vals: vals});
        }
        return vals[0] ? [vals[1]] : [vals[2]]
    }
    public MAX(vals: TAllTypes[]): TAllTypes[] {
        if(vals.length === 0) {
            throw new FormulaErr('#N/A', {vals: vals});
        }
        let result: IValueNumber = {
            type: 'value',
            dataType: 'number',
            value: 0
        }
        let res: IValueNumber|IValueString = vals.find(item => {
            return item.type === 'value' && (item.dataType === 'number' || item.dataType === 'string')
        }) as IValueNumber|IValueString;
        if(res && res.dataType === 'number') {
            result = res;
        }
        if(res.dataType === 'string') {
            result = parseToNumber(res)
        }
        for(let val of vals) {
            if(val.type === 'value' && (val.dataType === 'string' || val.dataType === 'number')) {
                let _val = parseToNumber(val);
                if(_val.value > result.value) {
                    result = _val;
                }
            }
        }
        return [result];
    }
    public MIN(vals: TAllTypes[]): TAllTypes[] {
        if(vals.length === 0) {
            throw new FormulaErr('#N/A', {vals: vals});
        }
        let result: IValueNumber = {
            type: 'value',
            dataType: 'number',
            value: 0
        }
        let res: IValueNumber|IValueString = vals.find(item => {
            return item.type === 'value' && (item.dataType === 'number' || item.dataType === 'string')
        }) as IValueNumber|IValueString;
        if(res.dataType === 'string') {
            result = parseToNumber(res)
        }
        for(let val of vals) {
            if(val.type === 'value' && (val.dataType === 'string' || val.dataType === 'number')) {
                let _val = parseToNumber(val);
                if(_val.value < result.value) {
                    result = _val;
                }
            }
        }
        return [result];
    }
    public COUNTIF(vals: TAllTypes[]): TAllTypes[] {
        let groupVals = formatValsToGroup(vals);
        if(groupVals.length !== 3) {
            throw new FormulaErr('#N/A', {vals: vals});
        }
        type valueFromCell = Required<IExcelValue>&(IValueBoolean|IValueNumber|IValueString);
        let rangeVals = <any>groupVals[0] as valueFromCell[];
        let criteria = <any>groupVals as (IValueBoolean|IValueNumber|IValueString)[];
        let group1 = groupByCol(rangeVals);
        let group2 = groupByCol(criteria as valueFromCell[]);
        let count = 0;
        if(criteria[0].rangeId !== undefined) { // 范围需要一一对应
            if(!Array.isArray(group1) ||!Array.isArray(group2)) {
                throw new FormulaErr('#N/A', {vals: vals, msg: 'group1,group2', groups: [group1, group2]});
            }
            for(let i = 0; i < Math.min(group1.length, group2.length); i++) {
                let rows1 = group1[i];
                let rows2 = group2[i];
                for(let cell of rows1.vals) {
                    let find = rows2.vals.find(item => item.row === cell.row);
                    if(!find) continue;
                    if(isEqualVal(cell, find)) {
                        count++;
                    }
                }
            }
        } else {
            if(criteria[0].col !== undefined || criteria[0].row !== undefined) { // 来源于单元格的值
                for(let i = 0; i < group1.length; i++) {
                    for(let cell of group1[i].vals) {
                        if(isEqualVal(cell, criteria[0])) {
                            count++;
                        }
                    }
                }
            } else { // 直接输入的值
                for(let i = 0; i < group1.length; i++) {
                    for(let cell of group1[i].vals) {
                        if(isEqualVal(cell, criteria[0])) {
                            count++;
                        }
                    }
                }
            }
        }
        return [{
            type: 'value',
            dataType: 'number',
            value: count
        }];
    }
    public STDEV(vals: TAllTypes[]): TAllTypes[] {
        return [];
    }
    public STDEVA(vals: TAllTypes[]): TAllTypes[] {
        return [];
    }

    public VLOOKUP(vals: TAllTypes[]): TAllTypes[] {
        return [];
    }
    public ROW(vals: TAllTypes[]): TAllTypes[] {
        let result = (<IExcelValue[]>vals).map<IValueNumber>((item) => {
            if(isNaN(Number(item.row))) {
                throw new FormulaErr("#N/A", {info: item, funcName: 'row'});
            }
            return {
                type: 'value',
                dataType: 'number',
                value: item.row
            }
        })
        return result;
    }
    public COLUMN(vals: TAllTypes[]): TAllTypes[] {
        let result = (<IExcelValue[]>vals).map<IValueNumber>((item) => {
            if(isNaN(Number(item.col))) {
                throw new FormulaErr("#N/A", {info: item, funcName: 'column'});
            }
            return {
                type: 'value',
                dataType: 'number',
                value: item.col
            }
        })
        return result;
    }
    public FIND(vals: TAllTypes[]): TAllTypes[] {
        if(vals.length > 0) {
            throw new FormulaErr('#DIV/0!', {info: vals, msg: 'find error params'});
        }
        let target = vals[0] as IValueString;
        let source = vals[1] as IValueString;
        let options = (vals[2] ? vals[2] : {type: 'value', dataType: 'number', value: 1})as IValueNumber;
        if(target.dataType !== 'string' || source.dataType !== 'string' || options.dataType !== 'number') {
            throw new FormulaErr('#DIV/0!', {info: vals, msg: 'find error params type'});
        }
        let idx = source.value.indexOf(target.value);
        if(idx === -1 || idx + 1 < options.value) {
            throw new FormulaErr('#VALUE!', {info: 'not find string index'});
        }
        return [{
            type: 'value',
            dataType: 'number',
            value: idx+1
        }];
    }
    public LOOKUP(vals: TAllTypes[]): TAllTypes[] {
        return [];
    }
    public MATCH(vals: TAllTypes[]): TAllTypes[] {

        return [];
    }
    public INDEX(vals: TAllTypes[]): TAllTypes[] {

        return [];
    }
    public SUMPRODUCT(vals: TAllTypes[]): TAllTypes[] {
        return [];
    }
    public RAND(vals: TAllTypes[]): TAllTypes[] {
        if(vals.length !== 0) {
            throw new FormulaErr('#VALUE!', {msg: 'rand not need params!'})
        }
        return [{
            type: 'value',
            dataType: 'number',
            value: Math.random()
        }];
    }
    public RANDBETWEEN(vals: TAllTypes[]): TAllTypes[] {
        if(vals.length !== 2 || typeofValue(vals[0]) !== 'number' || typeofValue(vals[1]) !== 'number') {
            throw new FormulaErr('#VALUE!', {msg:'randbetween params is not enough!params type is wrong!',vals: vals})
        }
        let val1 = Math.ceil((vals[0] as IValueNumber).value);
        let val2 = Math.ceil((vals[1] as IValueNumber).value);
        if(val1 >= val2) {
            throw new FormulaErr('#VALUE!', {msg:'param error', vals: vals})
        }
        return [{
            type: 'value',
            dataType: 'number',
            value: Math.random()*(val2-val1+1)+val1
        }];
    }
    public COUNTA(vals: TAllTypes[]): TAllTypes[] {
        let count = 0;
        for(let item of vals) {
            if(item.type === 'value' && item.dataType === 'string') {
                if(item.value !== '') count++;
            } else {
                count++;
            }
        }
        return [{
            type: 'value',
            dataType: 'number',
            value: count
        }];
    }
    public MID(vals: TAllTypes[]): TAllTypes[] {
        if(vals.length !== 3 || typeofValue(vals[0]) !== 'string' || typeofValue(vals[1]) !== 'number' || typeofValue(vals[2]) !== 'number') {
            throw new FormulaErr('#VALUE!', {msg: 'mid params is not enough ,or param type is wrong!', vals: vals});
        }
        let str = (vals[0] as IValueString).value;
        let startIdx = (vals[1] as IValueNumber).value;
        let len = (vals[2] as IValueNumber).value;
        if(startIdx < 0 || len < 0) {
            throw new FormulaErr('#VALUE!', {msg: 'mid params is error!',vals: vals});
        }
        return [{
            type: 'value',
            dataType: 'string',
            value: str.substring(startIdx - 1, (startIdx + len - 1) > str.length ? str.length : (startIdx + len - 1))
        }];
    }
    public MOD(vals: TAllTypes[]): TAllTypes[] {
        if(vals.length !== 2 || typeofValue(vals[0]) !== 'number' || typeofValue(vals[1]) !== 'number') {
            throw new FormulaErr('#VALUE!', {msg: 'mod params is not enough ,or param type is wrong!'});
        }
        let val1 = vals[0] as IValueNumber;
        let val2 = vals[1] as IValueNumber;
        return [{
            type: 'value',
            dataType: 'number',
            value: val1.value % val2.value 
        }];
    }
    public DATEDIF(vals: TAllTypes[]): TAllTypes[] {
        if(vals.length !== 3) {
            throw new FormulaErr('#VALUE!', {msg: 'datedif params is erorr!', vals: vals});
        }
        let optionParam = ['y','m','d','md','ym','yd'];
        let startValue = vals[0] as IValueNumber;
        let endValue = vals[1] as IValueNumber;
        let unitValue = vals[2] as IValueString;
        if((typeofValue(unitValue) !== 'string' || optionParam.indexOf(unitValue.value) === -1)
            || typeofValue(startValue) !== 'number' || startValue.value < 0
            || typeofValue(endValue) !== 'number' || endValue.value < 0
        ) {
            throw new FormulaErr('#VALUE!', {msg: 'datedif unit is erorr!', vals: vals});
        }
        let unit = ((vals[2] as IValueString).value) as 'y'|'m'|'d'|'md'|'ym'|'yd';
        let startDate = new Date(startValue.value * DAY_MUNIS);
        let endDate = new Date(endValue.value * DAY_MUNIS);
        if(unit === 'y') {
            return [{
                type: 'value',
                dataType: 'number',
                value: endDate.getFullYear() - startDate.getFullYear()
            }];
        }
        if(unit === 'm') {
            return [{
                type: 'value',
                dataType: 'number',
                value: (endDate.getFullYear() - startDate.getFullYear()) * 12 + (endDate.getMonth() - startDate.getMonth())
            }]
        }
        if(unit === 'd') {
            return [{
                type: 'value',
                dataType: 'number',
                value: endValue.value - startValue.value
            }]
        }
        if(unit === 'md') {
            throw new FormulaErr('#VALUE!', {msg: 'datedif not support yd、md、ym!'})
        }
        return [];
    }
    public TODAY(vals: TAllTypes[]): TAllTypes[] {
        let date = new Date();
        let dateToNum = date.getTime() / DAY_MUNIS;
        return [
            {
                type: 'value',
                dataType: 'number',
                value: dateToNum
            }
        ];
    }
    public TEXT(vals: TAllTypes[]): TAllTypes[] {
        let result = (vals as (IValueNumber|IValueString|any)[]).map(item => {
            if(item.dataType !== 'string' || item.dataType !== 'number') {
                throw new FormulaErr('#VALUE!', {info: 'function text value is not string or number', vals: vals})
            }
            return {
                type: 'value',
                dataType: 'string',
                value: String(item.value)
            }
        }) as TAllTypes[];
        return result;
    }
}